package com.tmp;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * 处理身份证信息
 */
public class ExcelProcessor {
    public static void main(String[] args) {
        String inputFilePath = "E:\\project\\prictise\\jdemo\\src\\main\\java\\com\\tmp\\aa.xls";
        String outputFilePath = "E:\\project\\prictise\\jdemo\\src\\main\\java\\com\\tmp\\output.xls";

        try (FileInputStream fis = new FileInputStream(inputFilePath);
             Workbook workbook = new HSSFWorkbook(fis);
             FileOutputStream fos = new FileOutputStream(outputFilePath)) {

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Row headerRow = sheet.getRow(0);
                rewriteExcel(sheet, headerRow);
            }
            workbook.write(fos);
           
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    private static void rewriteExcel(Sheet sheet,  Row headerRow ) throws IOException {
        // 假设身份证号在第二列，生日在第三列
        int idCardColumnIndex = 2;
        int birthdayColumnIndex = 3;
        int newColumnIndex = 5; // 新生成的列在第四列

        // 添加新列的标题
        Cell newHeaderCell = headerRow.createCell(newColumnIndex);
        newHeaderCell.setCellValue("身份");

        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell idCardCell = row.getCell(idCardColumnIndex);
                Cell birthdayCell = row.getCell(birthdayColumnIndex);

                if (idCardCell != null && birthdayCell != null) {
                    String idCard = getCellValue(idCardCell);
                    String birthday = getCellValue(birthdayCell);

                    String processedIdCard = processIdCard(idCard, birthday);

                    Cell newCell = row.createCell(newColumnIndex);
                    newCell.setCellValue(processedIdCard);
                }
            }
        }
    }

    private static String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return "";
        }
    }

    private static String processIdCard(String idCard, String birthday) {
        String tstr;
        if (idCard.startsWith("431")) {
            tstr =idCard.substring(0, 3)+"103" + birthday.replace("-", "").substring(0, 6) + idCard.substring(idCard.length()-6);
            return tstr;
        } else if (idCard.startsWith("432")) {
            tstr = idCard.substring(0, 3)+"902" + birthday.replace("-", "").substring(0, 6) + idCard.substring(idCard.length()-6);
            return tstr;
        }

        return idCard;
    }
}
